In [1]:
# import necessary libraries

import pandas as pd
import numpy as np
import holoviews as hv
import hvplot.pandas
import panel as pn
import plotly.express as px


from bokeh.models.formatters import NumeralTickFormatter
formatter = NumeralTickFormatter(format="0,0")
In [2]:
pn.extension('plotly')
hv.extension('bokeh')
In [3]:
orbis_scot_mfg_df = pd.read_pickle('Alex_work/data/orbis_data_with_postcode.pkl')
orbis_scot_mfg_df
Out[3]:
Company Size Postcode Employees_Last_Reported Employees_%Change TO_Last_Reported TO_2021 TO_2020 TO_%Change TO_%Change_2021 ... NHS_HA_code Admin_county_code Admin_district_code Admin_ward_code longitude latitude Employees_Last_Reported_Scaled TO_Last_Reported_Scaled productivity productivity_Scaled
0 LIFE TECHNOLOGIES LIMITED Large PA49RF 1212.0 8.0 2698116.0 NaN 2698116.0 126.0 NaN ... S08000031 NaN S12000038 S13003086 -4.445364 55.879283 0.109952 1.000000 2226.168317 0.254057
1 WEIR GROUP PLC (THE) Large G21RW NaN NaN 1978100.0 1978100.0 2286500.0 -13.0 -13.0 ... S08000031 NaN S12000049 S13002976 -4.255089 55.863100 0.000000 0.733141 NaN 0.000000
2 DIAGEO SCOTLAND LIMITED Large EH129HA 2944.0 -6.0 1814000.0 1814000.0 1424000.0 27.0 27.0 ... S08000024 NaN S12000036 S13002921 -3.313175 55.931966 0.267078 0.672321 616.168478 0.070319
3 HEINEKEN UK LIMITED Large EH129JZ 2368.0 -2.0 1098000.0 NaN 1098000.0 -24.0 NaN ... S08000024 NaN S12000036 S13002921 -3.299746 55.931991 0.214824 0.406951 463.682432 0.052917
4 CIRRUS LOGIC INTERNATIONAL (UK) LTD Large EH39EG 112.0 -10.0 981397.0 NaN 981397.0 -4.0 NaN ... S08000024 NaN S12000036 S13002929 -3.194934 55.943943 0.020000 0.363734 8762.473214 1.000000
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2709 VALHALLA CNC LTD Micro EH477AL NaN NaN 0.0 0.0 NaN NaN NaN ... S08000024 NaN S12000040 S13002826 -3.595575 55.876251 0.000000 0.020000 NaN 0.000000
2710 SILVA RERUM LTD Micro EH260FP NaN NaN 0.0 0.0 NaN NaN NaN ... S08000024 NaN S12000019 S13003021 -3.220973 55.843804 0.000000 0.020000 NaN 0.000000
2711 MAUCHIT LTD Micro PH27AU NaN NaN 0.0 NaN 0.0 -80.0 NaN ... S08000030 NaN S12000048 S13003063 -3.186821 56.417503 0.000000 0.020000 NaN 0.000000
2712 LUNE HANDMADE CRAFTS LIMITED Micro EH39NP NaN NaN 0.0 NaN 0.0 NaN NaN ... S08000024 NaN S12000036 S13002929 -3.209031 55.940553 0.000000 0.020000 NaN 0.000000
2713 WHITE THISTLE PRINTING LTD Micro EH153EG 1.0 NaN 0.0 0.0 NaN NaN NaN ... S08000024 NaN S12000036 S13002935 -3.114357 55.935902 0.020000 0.020000 0.000000 0.020000

2714 rows × 38 columns

In [4]:
idx=[]
for i in range (len(orbis_scot_mfg_df)):
    
    postcodes = str(orbis_scot_mfg_df['Postcode'].values[i]).split(',')
    selected_postcode = [item for item in postcodes if item.startswith('PH19') or (item.startswith('PH20')and item[4].isdigit()) or
                         item.startswith('PH22') or item.startswith('PH23') or item.startswith('PH24') or item.startswith('PH25') or
                         (item.startswith('PH26')and item[4].isdigit()) or item.startswith('PH30') or (item.startswith('PH31')and item[4].isdigit()) or 
                         item.startswith('PH32') or item.startswith('PH33') or item.startswith('PH34') or item.startswith('PH35') or 
                         item.startswith('PH36') or item.startswith('PH37') or item.startswith('PH38') or item.startswith('PH39') or
                         item.startswith('PH40') or (item.startswith('PH41')and item[4].isdigit()) or
                         item.startswith('PH42') or item.startswith('PH43') or item.startswith('PH44') or
                         item.startswith('IV') or 
                         item.startswith('KW') or item.startswith('HS') or item.startswith('ZE')]
    if len(selected_postcode)==0: 
        idx.append(i)
    else:
        orbis_scot_mfg_df['Postcode'].values[i] = selected_postcode[0]

orbis_scot_mfg_df.drop(orbis_scot_mfg_df.index[idx], axis=0, inplace=True)

highlands_mfg_df = orbis_scot_mfg_df
highlands_mfg_df
Out[4]:
Company Size Postcode Employees_Last_Reported Employees_%Change TO_Last_Reported TO_2021 TO_2020 TO_%Change TO_%Change_2021 ... NHS_HA_code Admin_county_code Admin_district_code Admin_ward_code longitude latitude Employees_Last_Reported_Scaled TO_Last_Reported_Scaled productivity productivity_Scaled
49 GLOBAL ENERGY (GROUP) LIMITED Large IV11SN 429.0 -27.0 118849.0 NaN 118849.0 -29.0 NaN ... S08000022 NaN S12000017 S13003005 -4.223005 57.488285 0.038919 0.044049 277.037296 0.031616
72 ALVANCE BRITISH ALUMINIUM LTD Large PH336TH 104.0 14.0 93500.0 NaN NaN 9.0 NaN ... S08000022 NaN S12000017 S13003010 -5.072608 56.829076 0.020000 0.034654 899.038462 0.102601
77 ALPHA SOLWAY LIMITED Large ZE10EB 315.0 320.0 88476.0 88476.0 16928.0 423.0 423.0 ... S08000026 NaN S12000027 S13002777 -1.143854 60.152931 0.028577 0.032792 280.876190 0.032054
82 ROSS-SHIRE ENGINEERING LIMITED Large IV67UA 834.0 12.0 81879.0 NaN 81879.0 8.0 NaN ... S08000022 NaN S12000017 S13002997 -4.453243 57.507271 0.075660 0.030347 98.176259 0.020000
92 AQUASCOT LIMITED Large IV170PJ 188.0 5.0 69385.0 NaN 69385.0 7.0 NaN ... S08000022 NaN S12000017 S13002995 -4.269383 57.690906 0.020000 0.025716 369.069149 0.042119
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2626 SCOTTISH TIMBER QUAICHS Micro IV243DJ 1.0 NaN 41.0 NaN 41.0 NaN NaN ... S08000022 NaN S12000017 S13002990 -4.352697 57.871167 0.020000 0.020000 41.000000 0.020000
2633 MASTER TAILORS (DINGWALL) LIMITED Micro IV11DR NaN NaN 40.0 NaN 40.0 NaN NaN ... S08000022 NaN S12000017 S13003003 -4.226869 57.478585 0.000000 0.020000 NaN 0.000000
2685 HEBRIDEAN PRESERVES AND PASTRIES LTD. Micro HS95UA NaN NaN 3.0 NaN 3.0 -44.0 NaN ... S08000028 NaN S12000013 S13002600 -7.441370 56.963055 0.000000 0.020000 NaN 0.000000
2687 RADDERY EQUINE LIMITED Micro IV108SN NaN NaN 3.0 NaN 3.0 -40.0 NaN ... S08000022 NaN S12000017 S13002998 -4.152225 57.603004 0.000000 0.020000 NaN 0.000000
2708 GOODIE GUTS LTD Micro PH337EW 1.0 NaN 0.0 0.0 NaN NaN NaN ... S08000022 NaN S12000017 S13003000 -5.100599 56.838639 0.020000 0.020000 0.000000 0.020000

253 rows × 38 columns

In [5]:
s='''
    red, yellow, orange, aqua, magenta,
    blue, brown, cadetblue,
    chartreuse, chocolate,
    darkcyan,
    darkgoldenrod, darkgray, darkgreen, darkmagenta, darkolivegreen, darkorange,
    darkorchid, darkred, darksalmon, darkseagreen,
    darkslateblue, darkslategray, darkslategrey,
    darkturquoise, darkviolet, deeppink, deepskyblue,
    dimgray, dimgrey, dodgerblue, firebrick,
    floralwhite, forestgreen, fuchsia, gainsboro,
    ghostwhite, gold, goldenrod, gray, grey, green,
    greenyellow, honeydew, hotpink, indianred, indigo,
    ivory, khaki, lavender, lavenderblush, lawngreen,
    lemonchiffon, lightblue, lightcoral, lightcyan,
    lightgoldenrodyellow, lightgray, lightgrey,
    lightgreen, lightpink, lightsalmon, lightseagreen,
    lightskyblue, lightslategray, lightslategrey,
    lightsteelblue, lightyellow, lime, limegreen,
    linen,  maroon, mediumaquamarine,
    mediumblue, mediumorchid, mediumpurple,
    mediumseagreen, mediumslateblue, mediumspringgreen,
    mediumturquoise, mediumvioletred, midnightblue,
    mintcream, mistyrose, moccasin, navajowhite, navy,
    oldlace, olive, olivedrab, orangered,
    orchid, palegoldenrod, palegreen, paleturquoise,
    palevioletred, papayawhip, peachpuff, peru, pink,
    plum, powderblue, purple,  rosybrown,
    royalblue, saddlebrown, salmon, sandybrown,
    seagreen, seashell, sienna, silver, skyblue,
    slateblue, slategray, slategrey, snow, springgreen,
    steelblue, tan, teal, thistle, tomato, turquoise,
    violet, wheat, white, whitesmoke,
    yellowgreen, aliceblue, antiquewhite, beige, bisque, black, blanchedalmond, coral, darkblue, cornsilk, burlywood, darkgrey, cyan, cornflowerblue, aquamarine, crimson,
    darkkhaki,
    blueviolet
    '''
colours = s.split(',')
colours = [c.replace('\n','') for c in colours]
colours = [c.replace(' ','') for c in colours]
colour_map = {bvd:c for bvd,c in zip(set(highlands_mfg_df['BvD']), colours)}
colour_map
Out[5]:
{'Printing & Publishing': 'red',
 'Miscellaneous Manufacturing': 'yellow',
 'Transport Manufacturing': 'orange',
 'Food & Tobacco Manufacturing': 'aqua',
 'Textiles & Clothing Manufacturing': 'magenta',
 'Industrial, Electric & Electronic Machinery': 'blue',
 'Wood, Furniture & Paper Manufacturing': 'brown',
 'Leather, Stone, Clay & Glass products': 'cadetblue',
 'Metals & Metal Products': 'chartreuse',
 'Chemicals, Petroleum, Rubber & Plastic': 'chocolate'}
In [6]:
rev_geog_pane = pn.pane.Markdown("""
# Revs by Location
""", width=200)

rev_geog_fig = px.scatter_mapbox(highlands_mfg_df, lat="latitude", lon="longitude", color="BvD", color_discrete_map=colour_map, zoom=6, mapbox_style='open-street-map',
                        hover_name='Company', hover_data=['Employees_Last_Reported','TO_Last_Reported','Postcode'], height=1200, width=1800, size='TO_Last_Reported_Scaled', size_max = 10)
rev_geog_fig.update_layout(legend = dict(font = dict(family = "Courier", size = 25, color = "black")),
                  legend_title = dict(font = dict(family = "Courier", size = 25, color = "blue")))
rev_geog_fig_pane = pn.pane.Plotly(rev_geog_fig)
rev_geog_layout = pn.Column(rev_geog_pane, rev_geog_fig_pane)
rev_geog_layout.servable()
Out[6]:
In [7]:
# Create widget for the panel
radio_buttons = pn.widgets.RadioButtonGroup(options=['Employees', 'Turnover'])

mfg_df_start = highlands_mfg_df.copy()
mfg_df_start.set_index("BvD",drop=True, append=False, inplace=True)

# Create panel function for radio buttons and checkboxes for SME and stacked individual company details
@pn.depends(radio_buttons)
def counts_emp_turnover(x, width = 1000, height = 1000, show_stacked = False, SME = False):
    
    # Defining employess plots
    if x == 'Employees':
        if SME:
            
            mfg_df = mfg_df_start[(mfg_df_start['Size'] != 'Large')]

            if show_stacked:     
                c2_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"Employees_Last_Reported": "sum"})
                c2_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
                c2_df = c2_df.sort_values(by='Total employees', ascending = False)

                c2_a = c2_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
                return(c2_a)
            else:
                c3_df = mfg_df.groupby(mfg_df.index).agg({"Employees_Last_Reported": "sum"})
                c3_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
                c3_df = c3_df.sort_values(by='Total employees', ascending = False)
                c3_a = c3_df[['Total employees']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
                return(c3_a)
        else:

            mfg_df = mfg_df_start

            if show_stacked:     
                c4_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"Employees_Last_Reported": "sum"})
                c4_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
                c4_df = c4_df.sort_values(by='Total employees', ascending = False)

                c4_a = c4_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
                return(c4_a)
            else:
                c5_df = mfg_df.groupby(mfg_df.index).agg({"Employees_Last_Reported": "sum"})
                c5_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
                c5_df = c5_df.sort_values(by='Total employees', ascending = False)
                c5_a = c5_df[['Total employees']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
                return(c5_a)
    
    # Defining turnover plots
    if x== 'Turnover':
        
        if SME:
            mfg_df = mfg_df_start[(mfg_df_start['Size'] != 'Large')]

            if show_stacked:     
                c6_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"TO_Last_Reported": "sum"})
                c6_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
                c6_df = c6_df.sort_values(by='Total turnover', ascending = False)

                c6_a = c6_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
                return(c6_a)
            else:
                c7_df = mfg_df.groupby(mfg_df.index).agg({"TO_Last_Reported": "sum"})
                c7_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
                c7_df = c7_df.sort_values(by='Total turnover', ascending = False)
                c7_a = c7_df[['Total turnover']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
                return(c7_a)
        else:

            mfg_df = mfg_df_start

            if show_stacked:     
                c8_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"TO_Last_Reported": "sum"})
                c8_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
                c8_df = c8_df.sort_values(by='Total turnover', ascending = False)

                c8_a = c8_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
                return(c8_a)
            else:
                c9_df = mfg_df.groupby(mfg_df.index).agg({"TO_Last_Reported": "sum"})
                c9_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
                c9_df = c9_df.sort_values(by='Total turnover', ascending = False)
                c9_a = c9_df[['Total turnover']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
                return(c9_a)
In [8]:
# Plot total number of employess and turnover for each sector(depending on SIC code) 
emp_db_explanation_pane = pn.pane.Markdown("""
# How many employees and how much turnover?
""", width=200)

# Create interactive panels with radio buttons and checkboxes
emp_db_chart_interact = pn.interact(counts_emp_turnover, x=radio_buttons)
emp_db_layout = pn.Row(emp_db_explanation_pane, emp_db_chart_interact)
emp_db_layout.servable()
Out[8]:
In [ ]: